set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;

-- 20230717update: 网点揽收运单未到中心,网点粒度指标不可以一行变多行
-- 没有到中心的运单,  从网点维表补上可交件中心，可能会一行变多行
with dim_network2 as (
    select  -- in_distribution_code_new 和 in_distribution_name_new 对应不上，先多关联一层
        explode_network.code
        ,explode_network.name
        ,network.code        as center_code
        ,network.name        as center_name
        ,pos
    from(
        select
            code
            ,name
            ,in_distribution_code_new
            ,in_distribution_name_new
            ,split(in_distribution_code_new, ',')[pos]       as center_code
            ,split(in_distribution_name_new, ',')[pos]       as center_name
            ,zone_code
            ,zone_name
            ,area_code
            ,area_name
            ,pos
        from
            jms_dim.dim_network_whole_massage LATERAL VIEW posexplode(split(in_distribution_code_new, ',')) t AS pos, id
        ) explode_network
            left join jms_dim.dim_network_whole_massage network
                    on explode_network.center_code = network.code

)
insert overwrite table jms_dm.dm_waybill_taking_hour_summary_dt partition (dt = '{{ execution_date | cst_ds }}')
select
    waybill.taking_date
    ,waybill.receive_network_code           --收件网点编码
    ,waybill.receive_network_name           --收件网点名称
    ,waybill.receive_franchisee_code        --收件加盟商编码
    ,waybill.receive_franchisee             --收件加盟商名称
    ,waybill.receive_proxy_code             --收件代理区编码
    ,waybill.receive_proxy                  --收件代理区名称
    ,coalesce(waybill.actual_center_code, dim_network2.center_code) as center_code     --实际交件转运中心
    ,coalesce(waybill.actual_center_name, dim_network2.center_name) as center_name     --实际交件转运中心
    ,dim_network1.zone_code                                                            -- 片区
    ,dim_network1.zone_name                                                            -- 片区
    ,dim_network1.area_code                                                            -- 底层片区
    ,dim_network1.area_name                                                            -- 底层片区
    ,count(*)                                      --揽收票数
    ,sum(if(waybill.is_interrupted = 1 , 1 , 0))   -- 截单后揽收票数
    ,sum(case when substr(waybill.actual_collect_time, 12, 2) >= 0  and substr(waybill.actual_collect_time, 12, 2) < 15 then 1 else 0 end ) as hour_segment1_taking_qty -- 时间段1揽收量(15点之前)
    ,sum(case when substr(waybill.actual_collect_time, 12, 2) >= 15 and substr(waybill.actual_collect_time, 12, 2) < 16 then 1 else 0 end ) as hour_segment2_taking_qty -- 时间段2揽收量(15点之前)
    ,sum(case when substr(waybill.actual_collect_time, 12, 2) >= 16 and substr(waybill.actual_collect_time, 12, 2) < 17 then 1 else 0 end ) as hour_segment3_taking_qty -- 时间段3揽收量(15点之前)
    ,sum(case when substr(waybill.actual_collect_time, 12, 2) >= 17 and substr(waybill.actual_collect_time, 12, 2) < 18 then 1 else 0 end ) as hour_segment4_taking_qty -- 时间段4揽收量(15点之前)
    ,sum(case when substr(waybill.actual_collect_time, 12, 2) >= 18 and substr(waybill.actual_collect_time, 12, 2) < 19 then 1 else 0 end ) as hour_segment5_taking_qty -- 时间段5揽收量(15点之前)
    ,sum(case when substr(waybill.actual_collect_time, 12, 2) >= 19 and substr(waybill.actual_collect_time, 12, 2) < 20 then 1 else 0 end ) as hour_segment6_taking_qty -- 时间段6揽收量(15点之前)
    ,sum(case when substr(waybill.actual_collect_time, 12, 2) >= 20 and substr(waybill.actual_collect_time, 12, 2) < 21 then 1 else 0 end ) as hour_segment7_taking_qty -- 时间段7揽收量(15点之前)
    ,sum(case when substr(waybill.actual_collect_time, 12, 2) >= 21 and substr(waybill.actual_collect_time, 12, 2) < 24 then 1 else 0 end ) as hour_segment8_taking_qty -- 时间段8揽收量(15点之前)
    ,case when dim_network2.pos >= 1 then 2 else 1 end as network_sum_flag            -- 是否可用于网点粒度汇总, 1表示可以用于网点粒度汇总, 2表示不可用于网点粒度汇总
from
    jms_dm.dm_waybill_collect_intime waybill
left join
    jms_dim.dim_network_whole_massage dim_network1
        on waybill.receive_network_code = dim_network1.code
left join
     dim_network2 -- 没有到中心的运单补上中心，可能会一行变多行
         on waybill.receive_network_code = dim_network2.code and waybill.actual_center_code is null
where
    waybill.dt = '{{ execution_date | cst_ds }}'
group by
    waybill.taking_date
    ,waybill.receive_network_code
    ,waybill.receive_network_name
    ,waybill.receive_franchisee_code
    ,waybill.receive_franchisee
    ,waybill.receive_proxy_code
    ,waybill.receive_proxy
    ,waybill.region_code
    ,waybill.region_name
    ,coalesce(waybill.actual_center_code, dim_network2.center_code)
    ,coalesce(waybill.actual_center_name, dim_network2.center_name)
    ,dim_network1.zone_code
    ,dim_network1.zone_name
    ,dim_network1.area_code
    ,dim_network1.area_name
    ,case when dim_network2.pos >= 1 then 2 else 1 end
distribute by 1
;